<?php
/**
 * This file is part of the Agora distribution. 
 * Detailed copyright and licensing information can be found
 * in the gpl-3.0.txt file which should be included in the distribution.
 * 
 * @version		$Id: 3.0.14 Olympus 2010-09-28 rammstein4o $
 * @copyright  2007 - 2010 jVitals
 * @license   GPLv3 Open Source
 * @link       http://jvitals.com
 * @since      File available since initial release
 */
defined ('IN_AGORA') or die;

class ServiceModel extends Model {

	function __construct() {
		parent::__construct();
	}

	function optimize() {
		$this->db->setQuery('SHOW TABLES LIKE \'##__%\'');
		$tables = $this->db->loadResultArray();
		foreach ($tables as $table) {
			$this->db->setQuery('OPTIMIZE TABLE '.$table);
			$this->db->query();
		}
	}

	function analyze() {
		$this->db->setQuery('SHOW TABLES LIKE \'##__%\'');
		$tables = $this->db->loadResultArray();
		foreach ($tables as $table) {
			$this->db->setQuery('ANALYZE TABLE '.$table);
			$this->db->query();
		}
	}

		/*
		function checkDatabase() {
			/// Fix category ordering

			$this->db->setQuery("SELECT id FROM {$this->_table('categories')}".
				" ORDER BY disp_position ASC, cat_name ASC");
			$categories = $this->db->loadResultArray();

			foreach ($categories as $num => $cat) {
				$pos = $num + 1;
				$this->db->setQuery("UPDATE {$this->_table('categories')} SET disp_position = {$pos} WHERE id = $cat");
				$this->db->query();
			}

			/// Reorder forums
			$this->db->setQuery("SELECT id, cat_id, parent_forum_id FROM {$this->_table('forums')}".
				" ORDER BY cat_id, parent_forum_id, disp_position ASC, forum_name ASC");
			$forums = $this->db->loadAssocList();

			$old_cat = -1;
			$old_parent = -1;
			foreach ($forums as $forum) {
				if ($old_cat != $forum['cat_id'] || $old_parent != $forum['parent_forum_id']) {
					$pos = 1;
				} else {
					$pos = $pos + 1;
				}
				$old_cat = $forum['cat_id'];
				$old_parent = $forum['parent_forum_id'];
				$this->db->setQuery("UPDATE {$this->_table('forums')} SET disp_position = {$pos} WHERE id = {$forum['id']}");
				$this->db->query();
			}


			/// Fix topics post count
			$this->db->setQuery("SELECT t.id, t.num_replies, COUNT(p.id) as post_count".
				" FROM {$this->_table('topics')} as t".
				" INNER JOIN {$this->_table('posts')} as p".
				" ON t.id=p.topic_id GROUP BY t.id");

			$topics = $this->db->loadAssocList();
			foreach ($topics as $topic) {
				if (intval($topic['num_replies']) != intval($topic['post_count'])) {
					$this->db->setQuery("UPDATE {$this->_table('topics')} SET num_replies = {$topic['post_count']} WHERE id = {$topic['id']}");
					$this->db->query();
				}
			}

			/// Fix forums topic count
			$this->db->setQuery("SELECT f.id, f.num_topics, COUNT(t.id) as topic_count".
				" FROM {$this->_table('forums')} as f".
				" INNER JOIN {$this->_table('topics')} as t".
				" ON f.id=t.forum_id GROUP BY f.id");

			$forums = $this->db->loadAssocList();

			foreach ($forums as $forum) {
				if (intval($forum['num_topics']) != intval($forum['topic_count'])) {
					$this->db->setQuery("UPDATE {$this->_table('forums')} SET num_topics = {$forum['topic_count']} WHERE id = {$forum['id']}");
					$this->db->query();
				}
			}

			/// Fix users post count
			$this->db->setQuery("SELECT u.id, u.num_posts, COUNT(p.id) as post_count".
				" FROM {$this->_table('users')} as u".
				" INNER JOIN {$this->_table('posts')} as p".
				" ON u.id=p.poster_id GROUP BY u.id");

			$users = $this->db->loadAssocList();

			foreach ($users as $user) {
				if (intval($user['num_posts']) != intval($user['post_count'])) {
					$this->db->setQuery("UPDATE {$this->_table('users')} SET num_posts = {$user['post_count']} WHERE id = {$user['id']}");
					$this->db->query();
				}
			}
		}*/

	function getPruneCount($days, $forum, $sticky) {
		$extra_sql = '';

		if ($forum > 0) {
			$extra_sql .= 'forum_id = '.intval($forum);
		}

		if ($days >= 0) {
			if (!empty($extra_sql)) {
				$extra_sql .= ' AND';
			}
			$extra_sql .= ' t.last_post < ' . (time()-$days*3600*24);
		}

		if (!$sticky) {
			if (!empty($extra_sql)) {
				$extra_sql .= ' AND';
			}
			$extra_sql .= ' sticky=\'0\'';
		}
		// Fetch topics to prune
		$this->setQuery('SELECT COUNT(*) as count, f.forum_name as forum_name FROM ##__topics as t'.
							' INNER JOIN ##__forums as f'.
							'  ON t.forum_id = f.id'.
							' WHERE '.$extra_sql.
							' GROUP BY forum_name');
		return $this->db->loadAssocList();
	}

	function prune($days, $forum, $sticky) {
		$extra_sql = '';

		if ($forum > 0) {
			$extra_sql .= 'forum_id = '.intval($forum);
		}

		if ($days >= 0) {
			if (!empty($extra_sql)) {
				$extra_sql .= ' AND';
			}
			$extra_sql .= ' last_post < ' . (time()-$days*3600*24);
		}

		if (!$sticky) {
			if (!empty($extra_sql)) {
				$extra_sql .= ' AND';
			}
			$extra_sql .= ' sticky=\'0\'';
		}
		// Fetch topics to prune
		$this->setQuery('SELECT id FROM ##__topics WHERE '.$extra_sql);

		$ids = $this->db->loadResultArray();
		$topic_ids = implode(',',$ids);
		$ids = null;
		$aff = 0;
		if ($topic_ids == '') return 0;

		$this->setQuery('SELECT forum_id, SUM(num_replies) AS num_replies, COUNT(*) AS num_topics FROM ##__topics WHERE id IN ('.$topic_ids.') GROUP BY forum_id');
		$forums = $this->db->loadAssocList();

		$this->setQuery('SELECT poster_id, poster, COUNT(*) AS num_posts FROM ##__posts WHERE topic_id IN ('.$topic_ids.') GROUP BY poster_id');
		$users = $this->db->loadAssocList();

		$this->setQuery('SELECT poster, COUNT(*) AS num_topics FROM ##__topics WHERE id IN ('.$topic_ids.') GROUP BY poster');
		$topic_starters = $this->db->loadAssocList('poster');

		// Fetch posts to prune
		$this->setQuery('SELECT id FROM ##__posts WHERE topic_id IN ('.$topic_ids.')');
		$ids = $this->db->loadResultArray();
		$post_ids = implode(',',$ids);
		$ids = null;

		if ($post_ids != '') {
			// Delete posts
			$this->setQuery('DELETE FROM ##__posts WHERE id IN('.$post_ids.')');
			$this->db->query();
		}

		// Delete topics
		$this->setQuery('DELETE FROM ##__topics WHERE id IN('.$topic_ids.')');
		$this->db->query();
		$aff = $this->db->getAffectedRows();

		// Delete subscriptions
		$this->setQuery('DELETE FROM ##__subscriptions WHERE topic_id IN('.$topic_ids.')');
		$this->db->query();

		// Delete ratings
		$this->setQuery('DELETE FROM ##__ratings WHERE topic_id IN('.$topic_ids.')');
		$this->db->query();

		// Delete read posts
		$this->setQuery('DELETE FROM ##__read_posts WHERE topic_id IN('.$topic_ids.')');
		$this->db->query();

		foreach ($forums as $forum) {
			$this->setQuery('SELECT COUNT(id) as count FROM ##__topics WHERE forum_id = ' . $forum['forum_id']);
			$last = $this->db->loadAssoc();
			
			$last_post = '';
			if ((int)$last['count'] == 0) {
				$last_post = ' ,last_post = null, last_post_id = null, last_poster = null ';
			}
			$this->setQuery('UPDATE ##__forums SET'.
								' num_posts = num_posts - '.(int)$forum['num_replies'].
								' ,num_topics = num_topics - '.(int)$forum['num_topics'].
								$last_post .
								' WHERE id='. $forum['forum_id']);
			$this->db->query();
		}

		foreach ($users as $user) {
			if (!isset($topic_starters[$user['poster']]['num_topics'])) {
				$topics = 0;
			} else {
				$topics = $topic_starters[$user['poster']]['num_topics'];
			}
			$this->setQuery('UPDATE ##__users SET num_posts = num_posts - '.($user['num_posts']-$topics).' WHERE id='.$user['poster_id']);
			$this->db->query();
		}

		return $aff;
	}
}
?>
